{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from zipline.pipeline import Pipeline\n",
    "from zipline.pipeline.data import USEquityPricing\n",
    "from sharadar.pipeline.factors import (\n",
    "    MarketCap,\n",
    "    EV,\n",
    "    Fundamentals,\n",
    "    Sector\n",
    ")\n",
    "from sharadar.pipeline.engine import load_sharadar_bundle, symbols, make_pipeline_engine\n",
    "from zipline.pipeline.filters import StaticAssets\n",
    "import time\n",
    "import datetime\n",
    "from sharadar.pipeline.universes import TRADABLE_STOCKS_US, NamedUniverse, TradableStocksUS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[2020-07-03 21:53:13.739733] INFO: sharadar_db_bundle: Compute pipeline values in chunks of 120 days.\n",
      "[2020-07-03 21:53:13.740576] INFO: sharadar_db_bundle: Compute values for pipeline from 2020-07-01 to 2020-07-01.\n",
      "[2020-07-03 21:53:13.746409] INFO: sharadar_db_bundle: Computing term 1 of 13 [AssetExists()]\n",
      "[2020-07-03 21:53:13.747258] INFO: sharadar_db_bundle: Term already in workspace: no computation needed\n",
      "[2020-07-03 21:53:13.747984] INFO: sharadar_db_bundle: Computing term 2 of 13 [Fundamentals{'field': 'sharesbas_arq'}]\n",
      "[2020-07-03 21:53:13.751792] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.003778\n",
      "[2020-07-03 21:53:13.752980] INFO: sharadar_db_bundle: Computing term 3 of 13 [Fundamentals{'field': 'sharefactor_arq'}]\n",
      "[2020-07-03 21:53:13.756230] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.003250\n",
      "[2020-07-03 21:53:13.757140] INFO: sharadar_db_bundle: Computing term 4 of 13 [Fundamentals{'field': 'revenue'}]\n",
      "[2020-07-03 21:53:13.759375] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.002246\n",
      "[2020-07-03 21:53:13.760142] INFO: sharadar_db_bundle: Computing term 5 of 13 [Sector([], 1)]\n",
      "[2020-07-03 21:53:13.764432] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.004298\n",
      "[2020-07-03 21:53:13.765484] INFO: sharadar_db_bundle: Computing term 6 of 13 [NamedUniverse([], 1)]\n",
      "[2020-07-03 21:53:13.773499] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.007948\n",
      "[2020-07-03 21:53:13.775102] INFO: sharadar_db_bundle: Computing term 7 of 13 [Fundamentals{'field': 'cashnequsd_arq'}]\n",
      "[2020-07-03 21:53:13.778223] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.003162\n",
      "[2020-07-03 21:53:13.779037] INFO: sharadar_db_bundle: Computing term 8 of 13 [Fundamentals{'field': 'evebit'}]\n",
      "[2020-07-03 21:53:13.781260] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.002216\n",
      "[2020-07-03 21:53:13.781997] INFO: sharadar_db_bundle: Computing term 9 of 13 [USEquityPricing.close::float64]\n",
      "[2020-07-03 21:53:13.784992] INFO: sharadar_db_bundle: Loading raw arrays for 9361 assets (<class 'pandas.core.indexes.numeric.Int64Index'>).\n",
      "[2020-07-03 21:53:13.845218] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.063196\n",
      "[2020-07-03 21:53:13.846212] INFO: sharadar_db_bundle: Computing term 10 of 13 [MarketCap([USEquityPricing.close, Fundamentals(...), Fundamentals(...)], 1)]\n",
      "[2020-07-03 21:53:13.848506] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.002290\n",
      "[2020-07-03 21:53:13.849373] INFO: sharadar_db_bundle: Computing term 11 of 13 [Fundamentals{'field': 'netmargin'}]\n",
      "[2020-07-03 21:53:13.851954] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.002574\n",
      "[2020-07-03 21:53:13.852747] INFO: sharadar_db_bundle: Computing term 12 of 13 [Fundamentals{'field': 'debtusd_arq'}]\n",
      "[2020-07-03 21:53:13.855104] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.002318\n",
      "[2020-07-03 21:53:13.856537] INFO: sharadar_db_bundle: Computing term 13 of 13 [EV([MarketCap(...), Fundamentals(...), Fundamentals(...)], 1)]\n",
      "[2020-07-03 21:53:13.858660] INFO: sharadar_db_bundle: Elapsed time: 0:00:00.002148\n"
     ]
    }
   ],
   "source": [
    "engine = make_pipeline_engine()\n",
    "\n",
    "pipe_date = pd.to_datetime('2020-07-01', utc=True)\n",
    "\n",
    "pipe = Pipeline(columns={\n",
    "    'mkt_cap': MarketCap(),\n",
    "    'ev': EV(),\n",
    "    'netmargin': Fundamentals(field='netmargin'),\n",
    "    'revenue': Fundamentals(field='revenue'),\n",
    "    'evebit': Fundamentals(field='evebit'),\n",
    "    'sector': Sector()\n",
    "},\n",
    "#screen = StaticAssets(symbols(['IBM', 'F', 'AAPL']))\n",
    "screen = NamedUniverse(TRADABLE_STOCKS_US)\n",
    "#screen = TradableStocksUS()\n",
    ")\n",
    "\n",
    "df = engine.run_pipeline(pipe, pipe_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>ev</th>\n",
       "      <th>evebit</th>\n",
       "      <th>mkt_cap</th>\n",
       "      <th>netmargin</th>\n",
       "      <th>revenue</th>\n",
       "      <th>sector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2020-07-01 00:00:00+00:00</th>\n",
       "      <th>Equity(109811 [MSGE])</th>\n",
       "      <td>1.060622e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.801448e+09</td>\n",
       "      <td>-0.643</td>\n",
       "      <td>1.998610e+08</td>\n",
       "      <td>Consumer Cyclical</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Equity(110003 [ARNC])</th>\n",
       "      <td>1.844107e+09</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.519107e+09</td>\n",
       "      <td>0.037</td>\n",
       "      <td>1.611000e+09</td>\n",
       "      <td>Industrials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Equity(110006 [CARR])</th>\n",
       "      <td>3.043305e+10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.924605e+10</td>\n",
       "      <td>0.025</td>\n",
       "      <td>3.888000e+09</td>\n",
       "      <td>Basic Materials</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Equity(110110 [RVMD])</th>\n",
       "      <td>1.690874e+09</td>\n",
       "      <td>-25.0</td>\n",
       "      <td>1.863069e+09</td>\n",
       "      <td>-1.883</td>\n",
       "      <td>1.154600e+07</td>\n",
       "      <td>Healthcare</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Equity(110121 [SDGR])</th>\n",
       "      <td>5.665788e+09</td>\n",
       "      <td>-77.0</td>\n",
       "      <td>5.796053e+09</td>\n",
       "      <td>-0.528</td>\n",
       "      <td>2.617400e+07</td>\n",
       "      <td>Technology</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                           ev  evebit  \\\n",
       "2020-07-01 00:00:00+00:00 Equity(109811 [MSGE])  1.060622e+09     NaN   \n",
       "                          Equity(110003 [ARNC])  1.844107e+09     4.0   \n",
       "                          Equity(110006 [CARR])  3.043305e+10     NaN   \n",
       "                          Equity(110110 [RVMD])  1.690874e+09   -25.0   \n",
       "                          Equity(110121 [SDGR])  5.665788e+09   -77.0   \n",
       "\n",
       "                                                      mkt_cap  netmargin  \\\n",
       "2020-07-01 00:00:00+00:00 Equity(109811 [MSGE])  1.801448e+09     -0.643   \n",
       "                          Equity(110003 [ARNC])  1.519107e+09      0.037   \n",
       "                          Equity(110006 [CARR])  1.924605e+10      0.025   \n",
       "                          Equity(110110 [RVMD])  1.863069e+09     -1.883   \n",
       "                          Equity(110121 [SDGR])  5.796053e+09     -0.528   \n",
       "\n",
       "                                                      revenue  \\\n",
       "2020-07-01 00:00:00+00:00 Equity(109811 [MSGE])  1.998610e+08   \n",
       "                          Equity(110003 [ARNC])  1.611000e+09   \n",
       "                          Equity(110006 [CARR])  3.888000e+09   \n",
       "                          Equity(110110 [RVMD])  1.154600e+07   \n",
       "                          Equity(110121 [SDGR])  2.617400e+07   \n",
       "\n",
       "                                                            sector  \n",
       "2020-07-01 00:00:00+00:00 Equity(109811 [MSGE])  Consumer Cyclical  \n",
       "                          Equity(110003 [ARNC])        Industrials  \n",
       "                          Equity(110006 [CARR])    Basic Materials  \n",
       "                          Equity(110110 [RVMD])         Healthcare  \n",
       "                          Equity(110121 [SDGR])         Technology  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sector\n",
       "Basic Materials           110\n",
       "Communication Services     20\n",
       "Consumer Cyclical         244\n",
       "Consumer Defensive        101\n",
       "Energy                    101\n",
       "Financial Services          0\n",
       "Healthcare                302\n",
       "Industrials               285\n",
       "NA                          0\n",
       "Real Estate                 0\n",
       "Technology                360\n",
       "Utilities                  62\n",
       "dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_group_by_sector = df.groupby('sector')\n",
    "sector_size = df_group_by_sector.size()\n",
    "sector_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# There are Sectors with size less than 30?\n",
    "len(sector_size[sector_size < 30]) > 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>ev</th>\n",
       "      <th>evebit</th>\n",
       "      <th>mkt_cap</th>\n",
       "      <th>netmargin</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2020-07-01 00:00:00+00:00</th>\n",
       "      <th>Equity(197000 [OGE])</th>\n",
       "      <td>0.580645</td>\n",
       "      <td>0.983871</td>\n",
       "      <td>0.532258</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.661290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Equity(124124 [ASH])</th>\n",
       "      <td>0.345455</td>\n",
       "      <td>0.981651</td>\n",
       "      <td>0.336364</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.518182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Equity(124103 [CARS])</th>\n",
       "      <td>0.893443</td>\n",
       "      <td>0.837500</td>\n",
       "      <td>0.987705</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.905738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Equity(198326 [NWL])</th>\n",
       "      <td>0.336634</td>\n",
       "      <td>0.885000</td>\n",
       "      <td>0.366337</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.376238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Equity(194516 [CXO])</th>\n",
       "      <td>0.257426</td>\n",
       "      <td>0.648515</td>\n",
       "      <td>0.237624</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.445545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                       ev    evebit   mkt_cap  \\\n",
       "2020-07-01 00:00:00+00:00 Equity(197000 [OGE])   0.580645  0.983871  0.532258   \n",
       "                          Equity(124124 [ASH])   0.345455  0.981651  0.336364   \n",
       "                          Equity(124103 [CARS])  0.893443  0.837500  0.987705   \n",
       "                          Equity(198326 [NWL])   0.336634  0.885000  0.366337   \n",
       "                          Equity(194516 [CXO])   0.257426  0.648515  0.237624   \n",
       "\n",
       "                                                 netmargin   revenue  \n",
       "2020-07-01 00:00:00+00:00 Equity(197000 [OGE])         1.0  0.661290  \n",
       "                          Equity(124124 [ASH])         1.0  0.518182  \n",
       "                          Equity(124103 [CARS])        1.0  0.905738  \n",
       "                          Equity(198326 [NWL])         1.0  0.376238  \n",
       "                          Equity(194516 [CXO])         1.0  0.445545  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sector_rank = df_group_by_sector.rank(ascending=False, pct=True, numeric_only=True)\n",
    "sector_rank.sort_values(by='netmargin', ascending=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2020-07-01 00:00:00+00:00  Equity(109811 [MSGE])    0.830943\n",
       "                           Equity(110003 [ARNC])    0.601677\n",
       "                           Equity(110006 [CARR])    0.212500\n",
       "                           Equity(110110 [RVMD])    0.712816\n",
       "                           Equity(110121 [SDGR])    0.736530\n",
       "dtype: float64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score = sector_rank.mean(axis=1)\n",
    "score.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
